package com.apes.fn.business.insurance.repository;

import com.apes.fn.business.insurance.modle.CommissionReconciliation;
import com.apes.framework.jpa.repository.CustomRepository;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @ClassName CommissionReconciliationRepository
 * @Author wsj
 * @Date 2021/6/17 17:25
 * @description 数据层
 */
public interface CommissionReconciliationRepository extends CustomRepository<CommissionReconciliation,String> {
    /**
     * 查询未对账得有效保单信息
     * @param supplier 供应商编码
     * @param startDate 区间,开始时间
     * @param endDate 区间,结束时间
     * @param commissionType 佣金类型
     * @return
     */
    @Query("select a.id as  policyNumber, to_char(a.issueDate,'yyyy-mm-dd') as issueOrderDate,a.plateLicense as carLicense," +
            " case when a.carOwnership = '1' then '个人' else '单位' end as carOwnership,to_char(a.startDate,'yyyy-mm-dd') as startDate," +
            " to_char(a.endDate,'yyyy-mm-dd') as endDate,a.insuranceAmount as insuranceAmount,a.netPremium as netPremium,0 as insuranceCommission," +
            " case a.insuranceType when '0' then '商业险' when  '1'  then '交强险' else '驾意险' end as insuranceType, " +
            " b.commissionRatio as commissionRatio,b.commission as commission,b.commissionType.name as commissionType, a.dept as dept , " +
            " a.city as city , a.province as province , a.dept as deptTemp" +
            " from PolicyInformation a,PolicyInformationItem b where a.id = b.policyInformation and a.supplier.id =:supplier" +
            " and a.createDate >=trunc(:startDate) and a.createDate <=trunc(:endDate) + 1 and b.commissionType.id in(:commissionType) and a.insuranceType in(:insuranceType) " +
            " and (b.settlement = false or (b.settlement = true and b.notBlendAmount > 3) ) ")
    List<Map<String, Object>> queryEffectivePolicyInformation(@Param("supplier") String supplier, @Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("commissionType") List commissionType, @Param("insuranceType") List insuranceType);

    /**
     * 查询未对账得有效保单信息
     * @param policyinformationId 保单号
     * @param commissionType 佣金类型
     * @return
     */
    @Query("select a.id as  policyNumber, to_char(a.issueDate,'yyyy-mm-dd') as issueOrderDate,a.plateLicense as carLicense," +
            " case when a.carOwnership = '1' then '个人' else '单位' end as carOwnership,to_char(a.startDate,'yyyy-mm-dd') as startDate," +
            " to_char(a.endDate,'yyyy-mm-dd') as endDate,a.insuranceAmount as insuranceAmount,a.netPremium as netPremium,0 as insuranceCommission," +
            " case a.insuranceType when '0' then '商业险' when  '1'  then '交强险' else '驾意险' end as insuranceType, " +
            " b.commissionRatio as commissionRatio,b.commission as commission,b.commissionType.name as commissionType, a.dept as dept " +
            " from PolicyInformation a,PolicyInformationItem b where a.id = b.policyInformation " +
            " and  b.commissionType.id = :commissionType and a.id in(:policyinformationId) " +
            " and (b.settlement = false or (b.settlement = true and b.notBlendAmount > 3) ) ")
    List<Map<String, Object>> queryEffectivePolicyInformation(@Param("policyinformationId") List policyinformationId, @Param("commissionType") String commissionType);

}
